﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CarLoanSystem.Entity;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class AlertsMessageInfoDAL : DBBase
    {
        public static AlertsMessageItem LoadAlertsMessage(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [ALERTS_MESSAGE_ID],[ALERTS_MESSAGE_DETAIL],[ALERTS_MESSAGE_STATUS],[LOAN_ID]
                ,[USERNAME] FROM [CAR_LOAN].[dbo].[ALERTS_MESSAGE] where [ALERTS_MESSAGE_ID] = @ALERTS_MESSAGE_ID", DBBase.con);
                command.Parameters.AddWithValue("@ALERTS_MESSAGE_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    AlertsMessageItem item = new AlertsMessageItem();
                    item.ALERTS_MESSAGE_DETAIL = dt.Rows[0]["ALERTS_MESSAGE_DETAIL"].ToString();
                    item.ALERTS_MESSAGE_ID = Convert.ToInt32(dt.Rows[0]["ALERTS_MESSAGE_ID"].ToString());
                    item.ALERTS_MESSAGE_STATUS = dt.Rows[0]["ALERTS_MESSAGE_STATUS"].ToString();
                    item.USER_NAME = dt.Rows[0]["USERNAME"].ToString();
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllAlertsMessageEmp(string id = "", string detail = "", string userName = "")
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                string query = @"SELECT [ALERTS_MESSAGE_ID],[ALERTS_MESSAGE_DETAIL],[ALERTS_MESSAGE_STATUS],[USERNAME]
                                FROM [CAR_LOAN].[dbo].[ALERTS_MESSAGE] where USERNAME = '" + userName + "' and ALERTS_MESSAGE_STATUS = 'A' ";
                if (id != "")
                {
                    query += " And ALERTS_MESSAGE_ID = " + id;
                }
                if (detail != "")
                {
                    query += " And ALERTS_MESSAGE_DETAIL like '%" + detail + "%'";
                }

                SqlCommand command = new SqlCommand(query, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllAlertsMessageCus(string userName = "")
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                string query = @"SELECT [ALERTS_MESSAGE_ID],[ALERTS_MESSAGE_DETAIL],[ALERTS_MESSAGE_STATUS],[USERNAME]
                                FROM [CAR_LOAN].[dbo].[ALERTS_MESSAGE] where USERNAME = '" + userName + "' and ALERTS_MESSAGE_STATUS = 'C' ";
                SqlCommand command = new SqlCommand(query, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(AlertsMessageItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[ALERTS_MESSAGE]
                                                       ([ALERTS_MESSAGE_DETAIL]
                                                       ,[ALERTS_MESSAGE_STATUS]
                                                       ,[USERNAME]
                                                       ,[LOAN_ID])
                                                 VALUES
                                                        (@ALERTS_MESSAGE_DETAIL
                                                       ,@ALERTS_MESSAGE_STATUS
                                                       ,@USERNAME
                                                       ,@LOAN_ID)", DBBase.con);

                command.Parameters.AddWithValue("@ALERTS_MESSAGE_DETAIL", item.ALERTS_MESSAGE_DETAIL);
                command.Parameters.AddWithValue("@ALERTS_MESSAGE_STATUS", item.ALERTS_MESSAGE_STATUS);
                command.Parameters.AddWithValue("@USERNAME", item.USER_NAME);
                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(AlertsMessageItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[ALERTS_MESSAGE]
                                                        SET [ALERTS_MESSAGE_STATUS] = @ALERTS_MESSAGE_STATUS
                                                    WHERE ([ALERTS_MESSAGE_ID] = @ALERTS_MESSAGE_ID )", DBBase.con);

                command.Parameters.AddWithValue("@ALERTS_MESSAGE_STATUS", item.ALERTS_MESSAGE_STATUS);
                command.Parameters.AddWithValue("@ALERTS_MESSAGE_ID", item.ALERTS_MESSAGE_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
